﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MapData;
using System.IO;
using System.Data.Common;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;

namespace ImportData
{
    public class ImportXLS : ImportMapDocument
    {

        public override MapDocument Load(string fileName) {
            return Append(fileName, new MapDocument());
        }

        public override MapDocument Append(string fileName, MapDocument doc) {
            if (!File.Exists(fileName))
            {
                //MessageBox.Show("file not found");
                throw new ImportException("File Does Not Exist");
            }
            /// Test to make sure the file is a .mif file.
            else if (!fileName.ToLower().EndsWith(".xls"))
            {
                throw new ImportException("File is not a Microsoft access speadsheet (.xls) file.");
            }


            
         //   string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+fileName+";ExtendedProperties=\"Excel 8.0\"";


            
            //MessageBox.Show("importing data");


            OleDbConnection connection = new OleDbConnection(ExcelConnection(fileName));
            connection.Open();

            System.Data.DataTable dt = null;
            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            // Add the sheet name to the string array.
            foreach (DataRow row in dt.Rows)
            {
                string strSheetTableName = row["TABLE_NAME"].ToString();
                excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
                i++;
            }
            String sheetName = "";
            if (excelSheets.Count() > 0)
                sheetName = excelSheets[0];

            //MessageBox.Show(sheetName);
            OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [2004 Election$]", connection);
            


            
            DbDataReader data = selectCommand.ExecuteReader();

            int ii = 0;
            while (data.Read())
            {
                ProcessRow(doc, data,ii);
                ii++;
            }


            connection.Close();

            return doc;
        }

        private void ProcessRow(MapDocument doc, DbDataReader data, int ii)
        {
            String district = null;

            try
            {
                district = data[0].ToString();


                MapObject[] ar = doc.getMapObjects();

                foreach (MapObject m in ar)
                {
                    if (m.Name.Equals(district))
                    {
                        String slnp = data[8].ToString();
                        String salp = data[9].ToString();

                        double lnp, alp = 0;
                        try
                        {
                            lnp = Double.Parse(slnp);
                            alp = Double.Parse(salp);

                            String party = (lnp > alp ? "LNP" : "ALP");
                            double percent = (lnp > alp ? lnp : alp);

                            m.Description += "<b>Party -</b> " + party+" ("+(lnp > alp ? slnp : salp)+"%)<br >";

                            if (m is Polygon)
                            {
                                Polygon p = (Polygon)m;
                                if (alp > lnp)
                                    p.FillColor = "990000FF";
                                else
                                    p.FillColor = "99FF0000";

                            }

                           // MessageBox.Show(m.Description);

                        }
                        catch (Exception e)
                        {
                            MessageBox.Show(e.ToString());
                        }

                    }
                }

                //MessageBox.Show(ii+" " +data[1].ToString() + data[2].ToString());// + data[3].ToString() + data[4].ToString() + data[5].ToString() + data[6].ToString());
                
                //party = data[4].ToString();
                //MessageBox.Show("district='"+district + "'," + party);
            }
            catch (Exception e)
            {

                MessageBox.Show(e.ToString());
            }
        }


        #region Excel Connection
        private string ExcelConnectionOptions()
        {
            string strOpts = "";
//            if (this.MixedData == true)
 //               strOpts += "Imex=2;";
//            if (this.Headers == true)
//                strOpts += "HDR=Yes;";
//            else
                strOpts += "HDR=No;";
            return strOpts;
        }



        private string ExcelConnection(string strExcelFileName)
        {
            return
                @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                @"Data Source=" + strExcelFileName + ";" +
                @"Extended Properties=" + Convert.ToChar(34).ToString() +
                @"Excel 8.0;" + ExcelConnectionOptions() + Convert.ToChar(34).ToString();
        }
        #endregion
    }
}
